'''
    数据库相关的操作

    pymysql 操作数据库   pip install pymysql
    python
'''
import pymysql

from futureloan.data import env


# db = {"host": "127.0.0.1", "port":3306, "user": "root", "pwd": "123456", "dbname": "future"}
def connect():
    try:
        a = pymysql.connect(user=env.db['user'],
                            password=env.db['pwd'],
                            host=env.db['host'],
                            database=env.db['dbname'],
                            port=env.db['port'],
                            charset="utf8")
        print(f"连接数据库{env.db['host']}:{env.db['port']}成功")
        return a
    except Exception as e:
        print(f"连接数据库异常，异常信息为：{e}")


def disconnect(conn):
    conn.close()


def execute(conn, sql):
    try:
        c = conn.cursor()
        c.execute(sql)
        conn.commit()
        r = c.fetchall() # fetch 取
        print(f"执行{sql}成功")
        return r
    except Exception as e:
        print(f"执行{sql}异常，异常信息为：{e}")
# 根据手机号码删除用户
def delete_user(phone):
    a = connect()
    execute(a, f"DELETE FROM member WHERE mobilephone='{phone}';")
    disconnect(a)
# 根据手机号码查询用户ID
def get_memberId(phone):
    a = connect()
    r = execute(a, f"select Id FROM member WHERE mobilephone='{phone}';")
    disconnect(a)
    # ((6,),)
    return r[0][0]

# 测试代码，用完可以删除
if __name__ == '__main__':
    # a = connect()  # 常见错误：port要求是整数    pwd要求是字符串   字符集为utf8，写utf-8会报错
    # execute(a, "DELETE FROM member WHERE mobilephone='18012345678';")
    # disconnect(a)
    print(get_memberId("15012345679"))
